#!/bin/bash
APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天

if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi
#

ads_order_by_category_sql="
insert overwrite table ads_order_by_category
select * from ads_order_by_category
union(
select
    '$do_date' dt,
    1 recent_day1,
    category_id,
    category_name,
    order_count_1d,
    order_num_1d,
    order_total_amount_1d
from dws_trade_course_category_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days1,
    category_id,
    category_name,
    if(recent_days1 = 7,order_count_7d,order_count_30d) order_count,
    if(recent_days1 = 7,order_num_7d,order_original_amount_30d) order_num,
    if(recent_days1 = 7,order_total_amount_7d,order_total_amount_30d) order_total_amount
from dws_trade_course_category_order_nd lateral view explode(array(7,30)) tmp1 as recent_days1
where dt='$do_date');
"

ads_order_by_subject_sql="
insert overwrite table ads_order_by_subject
select * from ads_order_by_subject
union(
select
    '$do_date' dt,
    1 recent_day2,
    subject_id,
    subject_name,
    order_count_1d,
    order_num_1d,
    order_total_amount_1d
from dws_trade_subject_course_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days2,
    subject_id,
    subject_name,
    if(recent_days2 = 7,order_count_7d,order_count_30d) order_count,
    if(recent_days2 = 7,order_user_count_7d,order_user_count_30d) order_num,
    if(recent_days2 = 7,order_total_amount_7d,order_total_amount_30d) order_total_amount
from dws_trade_subject_course_order_nd lateral view explode(array(7,30)) tmp2 as recent_days2
where dt='$do_date');
"


ads_category_videoplay_retention_sql="
  insert overwrite table ads_category_videoplay_retention
  select * from ads_category_videoplay_retention
  union
  select
      '$do_date' dt,
       1 recent_days,
      t1.category_id,
      category_name,
      sum(videoplay_count_1d) over (partition by t1.category_id) videoplay_count ,
      cast(order_count_1d / sum(videoplay_count_1d) over (partition by t1.category_id)*100 as decimal(16,2)) videoplay_retention_rate
  from
      (
          select
              category_id,
              category_name,
              course_id,
              videoplay_count_1d
          from dws_trffic_course_user_videoplay_1d
          where dt='$do_date'
      )t1
          join
      (
          select
              category_id,
              order_count_1d
          from dws_trade_course_category_order_1d
          where dt='$do_date'
      )t2
      on t1.category_id=t2.category_id
  union
  select
      '$do_date' dt,
      7 recent_days,
      t1.category_id,
      category_name,
      sum(videoplay_count_7d) over (partition by t1.category_id) videoplay_count ,
      cast(order_count_7d / sum(videoplay_count_7d) over (partition by t1.category_id) *100 as decimal(16,2)) videoplay_retention_rate
  from
      (
          select
              category_id,
              category_name,
              course_id,
              videoplay_count_7d,
              dt
          from dws_trffic_course_user_videoplay_nd
          where dt<='$do_date' and dt>=date_sub('$do_date',6)
      )t1
          join
      (
          select
              category_id,
              order_count_7d,
              dt
          from dws_trade_course_category_order_nd
          where dt<='$do_date' and dt>=date_sub('$do_date',6)
      )t2
      on t1.category_id=t2.category_id and t1.dt = t2.dt;
  "

ads_subject_idvideoplay_retention_sql="
  insert overwrite table ads_subject_idvideoplay_retention
  select * from ads_subject_idvideoplay_retention
  union
  select
      '$do_date' dt,
      1 recent_days,
      t1.subject_id,
      subject_name,
      sum(videoplay_count_1d) over (partition by t1.subject_id) videoplay_count ,
      cast(order_count_1d / sum(videoplay_count_1d) over (partition by t1.subject_id) *100 as decimal(16,2)) videoplay_retention_rate
  from
      (
          select
              subject_id,
              subject_name,
              course_id,
              videoplay_count_1d
          from dws_trffic_course_user_videoplay_1d
          where dt='$do_date'
      )t1
          join
      (
          select
              subject_id,
              order_count_1d
          from dws_trade_subject_course_order_1d
          where dt='$do_date'
      )t2
      on t1.subject_id=t2.subject_id
  union
  select
      '$do_date' dt,
      7 recent_days,
      t1.subject_id,
      subject_name,
      sum(videoplay_count_7d) over (partition by t1.subject_id) videoplay_count ,
      cast(order_count_7d / sum(videoplay_count_7d) over (partition by t1.subject_id) *100 as decimal(16,2)) videoplay_retention_rate
  from
      (
          select
              subject_id,
              subject_name,
              course_id,
              videoplay_count_7d,
              dt
          from dws_trffic_course_user_videoplay_nd
          where dt<='$do_date' and dt>=date_sub('$do_date',6)
      )t1
          join
      (
          select
              subject_id,
              order_count_7d,
              dt
          from dws_trade_subject_course_order_nd
          where dt<='$do_date' and dt>=date_sub('$do_date',6)
      )t2
      on t1.subject_id=t2.subject_id and t1.dt = t2.dt;"

ads_course_idvideoplay_retention_sql="
  insert overwrite table ads_subject_idvideoplay_retention
  select * from ads_subject_idvideoplay_retention
  union
  select
      '$do_date' dt,
      1 recent_days,
      t1.course_id,
      course_name,
      videoplay_count_1d videoplay_count ,
      cast(order_count_1d / videoplay_count_1d*100 as decimal(16,2)) videoplay_retention_rate
  from
      (
          select
              user_id,
              course_id,
              course_name,
              videoplay_count_1d
          from dws_trffic_course_user_videoplay_1d
          where dt='$do_date'
      )t1
          join
      (
          select
              course_id,
              order_count_1d
          from dws_course_order_1d
          where dt='$do_date'
      )t2
      on t1.course_id=t2.course_id
  union
  select
      '$do_date' dt,
      7 recent_days,
      t1.course_id,
      course_name,
      videoplay_count_7d dvideoplay_count ,
      cast(order_count_7d / videoplay_count_7d *100 as decimal(16,2)) videoplay_retention_rate
  from
      (
          select
              user_id,
              course_id,
              course_name,
              videoplay_count_7d,
              dt
          from dws_trffic_course_user_videoplay_nd
          where dt<='$do_date' and dt>=date_sub('$do_date',6)
      )t1
          join
      (
          select
              course_id,
              order_count_7d,
              dt
          from dws_course_order_nd
          where dt<='$do_date' and dt>=date_sub('$do_date',6)
      )t2
      on t1.course_id=t2.course_id and t1.dt = t2.dt;
  "

ads_new_buyer_stats_sql="
insert overwrite table ads_new_buyer_stats
select * from ads_new_buyer_stats
union
select
    '$do_date'dt ,
    odr.recent_days,
    new_order_user_count,
    new_payment_user_count
from
(
    select
        recent_days,
        sum(if(login_date_first>=date_add('$do_date',-recent_days+1),1,0)) new_order_user_count
    from dws_trade_user_order_nd lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)odr
join
(
    select
        recent_days,
        sum(if(login_date_first>=date_add('$do_date',-recent_days+1),1,0)) new_payment_user_count
    from dws_trade_user_payment_nd lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)pay
on odr.recent_days=pay.recent_days;

"

ads_user_action_sql="
insert overwrite table ads_user_action
select * from ads_user_action
union
select
    '$do_date' dt,
    page.recent_days,
    home_count,
    course_detail_count,
    cart_count,
    order_count,
    payment_count
from
(
    select
        1 recent_days,
        sum(if(page_id='home',1,0)) home_count,
        sum(if(page_id='course_detail',1,0)) course_detail_count
    from dws_traffic_page_visitor_page_view_1d
    where dt='$do_date'
    and page_id in ('home','course_detail')
    union all
    select
        recent_days,
        sum(if(page_id='home' and view_count>0,1,0)),
        sum(if(page_id='course_detail' and view_count>0,1,0))
    from
    (
        select
            recent_days,
            page_id,
            case recent_days
                when 7 then view_count_7d
                when 30 then view_count_30d
            end view_count
        from dws_traffic_page_visitor_page_view_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
        and page_id in ('home','course_detail')

    )t1
    group by recent_days
)page
join
(
    select
        1 recent_days,
        count(*) cart_count
    from dws_trade_user_cart_add_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(cart_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then cart_add_count_7d
                when 30 then cart_add_count_30d
            end cart_count
        from dws_trade_user_cart_add_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)cart
on page.recent_days=cart.recent_days
join
(
    select
        1 recent_days,
        count(*) order_count
    from dws_trade_user_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
        from dws_trade_user_order_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)ord
on page.recent_days=ord.recent_days
join
(
    select
        1 recent_days,
        count(*) payment_count
    from dws_trade_user_payment_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then payment_count_7d
                when 30 then payment_count_30d
            end order_count
        from dws_trade_user_payment_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)pay
on page.recent_days=pay.recent_days;
"

ads_age_order_sql="
insert overwrite table ads_age_order
select * from ads_age_order
union
select
    '$do_date' dt,
       recent_days,
       age_group,
       case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
from dws_user_age_order_nd lateral view explode(array(1,7,30)) tmp as recent_days
where dt = '$do_date'

"

ads_course_order="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.ads_course_order
select * from ${APP}.ads_course_order
union
select '$do_date' dt,
       t1.course_id,
       t1.course_name,
       order_count_1d,
       order_original_amount_1d,
       activity_reduce_amount_1d,
       order_total_amount_1d,
       user_count_1d,order_count_7d,
       order_original_amount_7d,
       activity_reduce_amount_7d,
       order_total_amount_7d,
       user_count_7d,
       order_count_30d,
       order_original_amount_30d,
       activity_reduce_amount_30d,
       order_total_amount_30d,
       user_count_30d
from (select
          course_id,
          course_name,
          count(distinct(order_id)) order_count_1d,
          sum(origin_amount ) order_original_amount_1d,
          sum(coupon_reduce ) activity_reduce_amount_1d,
          sum(final_amount  ) order_total_amount_1d,
          count(distinct user_id) user_count_1d
      from dwd_trade_order_detail_inc
      where dt='$do_date'
      group by course_id,course_name) t1
         left join (select
                        course_id,
                        sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)) order_count_7d,
                        sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)) order_original_amount_7d,
                        sum(if(dt>=date_add('$do_date',-6),activity_reduce_amount_1d,0)) activity_reduce_amount_7d,
                        sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)) order_total_amount_7d,
                        sum(if(dt>=date_add('$do_date',-6),user_count_1d,0)) user_count_7d,
                        sum(order_count_1d) order_count_30d,
                        sum(order_original_amount_1d ) order_original_amount_30d,
                        sum(activity_reduce_amount_1d ) activity_reduce_amount_30d,
                        sum(order_total_amount_1d  ) order_total_amount_30d,
                        sum(user_count_1d) user_count_30d
                    from dws_course_order_1d
                    where dt>=date_add('$do_date',-29)
                    group by course_id
) t2
                   on  t1.course_id=t2.course_id;
"

ads_course_review="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.ads_course_review
select * from ${APP}.ads_course_review
union
select '$do_date' dt             ,
       t3.course_id               ,
       user_review_sum_1d        ,
       user_review_count_1d      ,
       user_avg_review_1d        ,
       positive_ratings_1d       ,
       user_review_sum_7d        ,
       user_review_count_7d      ,
       user_avg_review_7d        ,
       positive_ratings_7d       ,
       user_review_sum_30d       ,
       user_review_count_30d     ,
       user_avg_review_30d       ,
       positive_ratings_30d
from (select t1.course_id,
             user_review_sum_1d,
             user_review_count_1d,
             user_review_sum_1d/user_review_count_1d user_avg_review_1d,
             good_review_count_1d/user_review_count_1d positive_ratings_1d
      from (select
                course_id,
                sum(review_stars ) user_review_sum_1d,
                count(distinct (user_id)) user_review_count_1d,
                count(if(review_stars=="5",1,0)) good_review_count_1d
            from dwd_course_evaluate_inc
            where dt='$do_date'
            group by course_id
           ) t1
    ) t3
 left join (select
                        course_id,
                        sum(if(dt>=date_add('$do_date',-6),user_review_sum_1d,0)) user_review_sum_7d,
                        sum(if(dt>=date_add('$do_date',-6),user_review_count_1d,0)) user_review_count_7d,
                        sum(if(dt>=date_add('$do_date',-6),user_review_sum_1d,0))/sum(if(dt>=date_add('$do_date',-6),user_review_count_1d,0)) user_avg_review_7d,
                        sum(if(dt>=date_add('$do_date',-6),good_review_count_1d,0))/sum(if(dt>=date_add('$do_date',-6),user_review_count_1d,0)) positive_ratings_7d,
                        sum(user_review_sum_1d) user_review_sum_30d,
                        sum(user_review_count_1d ) user_review_count_30d ,
                        sum(user_review_sum_1d)/sum(user_review_count_1d)  user_avg_review_30d,
                        sum(good_review_count_1d)/sum(user_review_count_1d)  positive_ratings_30d
                    from (
                             select  course_id,
                                      dt,
                                     sum(review_stars ) user_review_sum_1d,
                                     count(distinct (user_id)) user_review_count_1d,
                                     count(if(review_stars=="5",1,0)) good_review_count_1d
                             from dwd_course_evaluate_inc
                             where dt='$do_date'
                             group by course_id,dt
                          ) t4
                    where dt>=date_add('$do_date',-29)
                    group by course_id
            ) t2
                   on  t3.course_id=t2.course_id;
"

ads_play_by_chapter_sql="
insert overwrite table ads_play_by_chapter
select
      '$do_date',
       recent_days,
       chapter_name,
      count(1)   play_count,
       avg(play_sec) avg_duration_sec,
       count(distinct uid) uv_count
from dwd_videoplay_full
lateral view explode(array(1,7,30)) tmp as recent_days
where dt <='$do_date' and dt >= date_sub('$do_date',recent_days-1)
group by chapter_name,recent_days;
"

ads_play_by_course_sql="
insert overwrite table ads_play_by_course
select
      '$do_date',
       recent_days,
       course_name,
      count(1)   play_count,
       avg(play_sec) avg_duration_sec,
       count(distinct uid) uv_count
from dwd_videoplay_full
lateral view explode(array(1,7,30)) tmp as recent_days
where dt <='$do_date' and dt >= date_sub('$do_date',recent_days-1)
group by course_name,recent_days;
"

ads_test_paper_sql="
insert overwrite table ads_test_paper partition (dt = '$do_date')
select paper_id,
       avg_score_1d,
       sum(\`if\`(dt > date_sub(dt, 7), total_score_1d, 0)) /
       sum(\`if\`(dt > date_sub(dt, 7), total_user_1d, 0))         avg_score_7d,
       sum(total_score_1d) / sum(total_score_1d)                 avg_score_30d,
       avg_duration_sec_1d,
       sum(\`if\`(dt > date_sub(dt, 7), total_duration_sec_1d, 0)) /
       sum(\`if\`(dt > date_sub(dt, 7), total_user_1d, 0))         avg_duration_sec_7d,
       sum(total_duration_sec_1d) / sum(total_user_1d)           avg_duration_sec_30d,
       total_user_1d,
       sum(\`if\`(dt > date_sub(dt, 7), total_duration_sec_1d, 0)) total_user_7d,
       sum(total_user_1d)                                        total_user_30d
from dws_course_paper_test_paper_1d
where dt <= '$do_date'
  and dt > date_sub('$do_date', 30)
group by paper_id, avg_score_1d, avg_duration_sec_1d, total_user_1d;
"

ads_test_course_sql="
insert overwrite table ads_test_course partition (dt = '$do_date')
select course_id,
       avg_course_score_1d,
       avg_course_duration_sec_1d,
       total_course_user_1d,
       sum(\`if\`(dt > date_sub(dt, 7), total_course_score_1d, 0)) / sum(\`if\`(dt > date_sub(dt, 7), total_course_user_1d, 0))         avg_course_score_7d,
       sum(\`if\`(dt > date_sub(dt, 7), total_course_duration_sec_1d, 0)) / sum(\`if\`(dt > date_sub(dt, 7), total_course_user_1d, 0))  avg_course_duration_sec_7d,
       sum(\`if\`(dt > date_sub(dt, 7), total_course_duration_sec_1d, 0))                                                             total_course_user_7d,
       sum(total_course_score_1d) / sum(total_course_score_1d)                                                                      avg_course_score_30d,
       sum(total_course_duration_sec_1d) / sum(total_course_user_1d)                                                                avg_course_duration_sec_30d,
       sum(total_course_user_1d)                                                                                                    total_course_user_30d
from dws_course_course_test_course_1d
where dt <= '$do_date'
  and dt > date_sub('$do_date', 30)
group by course_id, avg_course_score_1d, avg_course_duration_sec_1d, total_course_user_1d;
"

ads_page_path_sql="insert overwrite table ads_page_path
select * from ads_page_path
union
select '$do_date' dt,
       source,
       nvl(target, 'null'),
       count(*)     path_count
from (
         select concat('step-', rn, ':', page_id)          source,
                concat('step-', rn + 1, ':', next_page_id) target
         from (
                  select session_id,
                         page_id,
                         lead(page_id, 1, null) over (partition by session_id order by view_time) next_page_id,
                         row_number() over (partition by session_id order by view_time)           rn
                  from dwd_traffic_page_view_inc
                  where dt = '$do_date' and session_id is not null
              ) t1
     ) t2
group by source, target;"

ads_traffic_order_by_channel_sql="insert overwrite table ads_traffic_order_by_channel
select * from ads_traffic_order_by_channel
union
select
    '$do_date' dt,
    recent_days ,
     channel,
    sum(if(order_status=1002,final_amount,0)), --计算给钱的人，判断下单状态
    count(if(page_item_type ='order_id',1,null))/count(*)
from dws_traffic_session_page_order_1d
lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt>date_sub('$do_date',recent_days)
group by channel, recent_days, '$do_date';"

ads_traffic_stats_by_channel_sql="insert overwrite table ads_traffic_stats_by_channel
select * from ads_traffic_stats_by_channel
union
select
    '$do_date' dt,
    recent_days,
    channel,
    cast(count(distinct(mid_id)) as bigint) uv_count,
    cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec,
    cast(avg(page_count_1d) as bigint) avg_page_count,
    cast(count(*) as bigint) sv_count,
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate --跳出率计算
from dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
where dt>date_sub('$do_date',recent_days)
group by recent_days,channel;"



ads_order_integration_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ads_order_integration
select * from ads_order_integration
union
select
    '$do_date' dt,
    1 recent_days,
    order_users_num_1d,
    order_count_1d,
    order_total_amount_1d
from dws_trade_all_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    case recent_days
        when 7 then order_users_num_7d
        when 30 then order_users_num_30d
    end order_users_num,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from dws_trade_all_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"

ads_order_by_province_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ads_order_by_province
select * from ads_order_by_province
union
select
    '$do_date' dt,
    1 recent_days,
    province_id,
    province_name,
    order_users_num_1d,
    order_count_1d,
    order_total_amount_1d
from dws_trade_province_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    province_id,
    province_name,
    case recent_days
        when 7 then order_users_num_7d
        when 30 then order_users_num_30d
    end order_users_num,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_total_amount_7d
        when 30 then order_total_amount_30d
    end order_total_amount
from dws_trade_province_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date'
order by recent_days;
"

ads_finish_by_course="
use edu;
insert overwrite table ${APP}.ads_finish_by_course
select '$do_date',
       course_name,
       sum(avg_num_chapter_1d) avg_num_chapter_1d,
       sum(count_user_1d) count_user_1d,
       sum(avg_num_chapter_7d) avg_num_chapter_7d,
       sum(count_user_7d) count_user_7d,
       sum(avg_num_chapter_30d) avg_num_chapter_30d,
       sum(count_user_30d) count_user_30d
from (
         select course_name,
                sum(avg_num)    avg_num_chapter_1d, 
                sum(count_user) count_user_1d,     
                0               avg_num_chapter_7d,  
                0               count_user_7d,      
                0               avg_num_chapter_30d, 
                0               count_user_30d      
         from (
                  select course_name,
                         round(sum(is_finish) / count(distinct uid), 1) avg_num,
                         0                                              count_user
                  from dws_traffic_user_chapter_videoplay_1d
                  where dt = '$do_date'
                  group by course_name
                  union
                  select course_name,
                         0                   avg_num,
                         count(distinct uid) count_user
                  from (
                           select course_name,
                                  uid
                           from dws_traffic_user_course_videoplay_1d
                           where is_finish = 1
                             and dt = '$do_date'
                           group by course_name, uid) t1
                  group by course_name
              ) s1
         group by course_name

         union all


         select course_name,
                0               avg_num_chapter_1d,  
                0               count_user_1d,       
                sum(avg_num)    avg_num_chapter_7d,  
                sum(count_user) count_user_7d,       
                0               avg_num_chapter_30d, 
                0               count_user_30d      
         from (
                  select course_name,
                         round(sum(is_finish) / count(distinct uid), 1) avg_num,
                         0                                              count_user
                  from dws_traffic_user_chapter_videoplay_1d
                  where dt >= date_sub('$do_date', 6)
                  group by course_name
                  union
                  select course_name,
                         0                   avg_num,
                         count(distinct uid) count_user
                  from (
                           select course_name,
                                  uid
                           from dws_traffic_user_course_videoplay_1d
                           where is_finish = 1
                             and dt >= date_sub('$do_date', 6)
                           group by course_name, uid) t1
                  group by course_name
              ) s1
         group by course_name

         union all


         select course_name,
                0               avg_num_chapter_1d, 
                0               count_user_1d,      
                0               avg_num_chapter_7d, 
                0               count_user_7d,      
                sum(avg_num)    avg_num_chapter_30d, 
                sum(count_user) count_user_30d       
         from (
                  select course_name,
                         round(sum(is_finish) / count(distinct uid), 1) avg_num,
                         0                                              count_user
                  from dws_traffic_user_chapter_videoplay_1d
                  where dt >= date_sub('$do_date', 29)
                  group by course_name
                  union
                  select course_name,
                         0                   avg_num,
                         count(distinct uid) count_user
                  from (
                           select course_name,
                                  uid
                           from dws_traffic_user_course_videoplay_1d
                           where is_finish = 1
                             and dt >= date_sub('$do_date', 29)
                           group by course_name, uid) t1
                  group by course_name
              ) s1
         group by course_name
     ) s2
group by course_name;"

ads_finish_course_sum_sql="use edu;
insert overwrite table ads_finish_course_sum
select '$do_date',
       course_name,
       sum(count_1d),
       sum(count_user_1d),
       sum(count_7d),
       sum(count_user_7d),
       sum(count_30d),
       sum(count_user_30d)
from (
         select course_name,
                count(1)            count_1d,
                count(distinct uid) count_user_1d,
                0                   count_7d,
                0                   count_user_7d,
                0                   count_30d,
                0                   count_user_30d
         from (
                  select course_name,
                         uid
                  from dws_traffic_user_course_videoplay_1d
                  where is_finish = 1
                    and dt = '$do_date'
                  group by course_name, uid
              ) t1
         group by course_name

         union all

         select course_name,
                0                   count_1d,
                0                   count_user_1d,
                count(1)            count_7d,
                count(distinct uid) count_user_7d,
                0                   count_30d,
                0                   count_user_30d
         from (
                  select course_name,
                         uid
                  from dws_traffic_user_course_videoplay_1d
                  where is_finish = 1
                    and dt >= date_sub('$do_date', 6)
                  group by course_name, uid
              ) t1
         group by course_name

         union all
         select course_name,
                0                   count_1d,
                0                   count_user_1d,
                0                   count_7d,
                0                   count_user_7d,
                count(1)            count_30d,
                count(distinct uid) count_user_30d
         from (
                  select course_name,
                         uid
                  from dws_traffic_user_course_videoplay_1d
                  where is_finish = 1
                    and dt >= date_sub('$do_date', 29)
                  group by course_name, uid
              ) t1
         group by course_name
     )ss
group by course_name;"

ads_user_change="
	insert overwrite table ads_user_change
	select * from ads_user_change
	union
	select
			'$do_date' dt,
			user_churn_count,
			user_back_count
	from (
	--流失用户(上一次登录是7天前)
			select count(user_id) user_churn_count
			from dws_user_user_login_td
			where login_date_last = date_sub('$do_date', 7)
			and dt ='$do_date'
		)t3 join (
		--回流用户
			select
					count(*) user_back_count
			from (
					--到昨天为止，7天以上没登录的用户
					select user_id
					from dws_user_user_login_td
					where login_date_last = date_sub('$do_date', 8)
					and dt <= date_sub('$do_date', 1)
				) t
					join(
				--今天登录的用户
				select user_id
				from dws_user_user_login_td
				where login_date_last = '$do_date'
				and dt = '$do_date'
		) t2
	)t4;
"

ads_user_retention="
	insert overwrite table ads_user_retention
	select * from ads_user_retention
	union
	select
		'$do_date' dt,
		creat_time create_date,
		datediff('$do_date',creat_time) retention_day,---留存天数 = 最后一次登录天数 - 注册天数
		count(login.user_id) retention_count,
		count(reg.user_id) new_user_count,
		cast(count(login.user_id)/count(reg.user_id) as decimal(16,2))retention_rate
	
	from (
			--截止到06-14所有登录的人
			select
					user_id
			from dwd_user_login_inc
			where
				dt = '$do_date'
			group by
				user_id
		)login right join
		(
			--在06-07至06-13之间注册的人
			select dt creat_time,
					user_id
			from dwd_user_register_inc
			where dt between date_sub('$do_date', 7) and date_sub('$do_date',1)
		)reg
			on login.user_id = reg.user_id
	group by creat_time;
"

ads_user_stats="
	insert overwrite table ads_user_stats
	select * from ads_user_stats
	union
	select
		'$do_date' dt,
		nvl(t1.recent_days,t.recent_days) recent_days,
		nvl(new_user_count,0) new_user_count,
		nvl(active_user_count,0) active_user_count
	from (
			--最近1/7/30增用户统计
			select recent_days,
					count(*) new_user_count
			from dwd_user_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
			where
				dt > date_sub('$do_date', 30)
			and dt > date_sub('$do_date', recent_days)
			group by recent_days
		) t full join
		(
			--最近1/7/30天内活跃的用户
			select recent_days,
					count(*) active_user_count
			from dws_user_user_login_td lateral view explode(array(1, 7, 30)) tmp as recent_days
			where --1.先过滤30天内的数据
				login_date_last > date_sub('$do_date', 30)
			-- 分组后，过滤在所在分组的日期范围内登录的用户
			and login_date_last > date_sub('$do_date', recent_days)
			and dt = '$do_date'
			group by recent_days
		)t1
			on t.recent_days = t1.recent_days;"
ads_paper_score_page_sql="
insert overwrite table ads_paper_score_page 
select 
	'$do_date' dt,
	paper_id, 
       paper_title,
       page_score, score_page_1d, 
       score_page_7d, 
       score_page_30d
from dws_paper_score_page_nd
where dt = '$do_date';
"
ads_question_accuracy_sql="
insert overwrite table ads_question_accuracy
select 
	'$do_date' dt,
	question_id,
       question_accuracy_1d,
       question_accuracy_7d,
       question_accuracy_30d
from dws_question_accuracy_nd
where dt='$do_date';
"

case $1 in
"ads_order_by_subject")
	/opt/module/hive/bin/hive -e "use edu;$ads_order_by_subject_sql"
;;
"ads_order_by_category")
	/opt/module/hive/bin/hive -e "use edu;$ads_order_by_category_sql"
;;
"ads_category_videoplay_retention_sql")
    /opt/module/hive/bin/hive -e "use edu;$ads_category_videoplay_retention_sql"
;;
"ads_subject_idvideoplay_retention_sql")
    /opt/module/hive/bin/hive -e "use edu;$ads_subject_idvideoplay_retention_sql"
;;
"ads_course_idvideoplay_retention_sql")
    /opt/module/hive/bin/hive -e "use edu;$ads_course_idvideoplay_retention_sql"
;;
"ads_traffic_stats_by_channel")
	/opt/module/hive/bin/hive -e "use edu;$ads_traffic_stats_by_channel_sql"
;;
"ads_traffic_order_by_channel")
	/opt/module/hive/bin/hive -e "use edu;$ads_traffic_order_by_channel_sql"
;;
"ads_page_path")
	/opt/module/hive/bin/hive -e "use edu;$ads_page_path_sql"
;;
"ads_course_order" )
    /opt/module/hive/bin/hive -e "use edu;$ads_course_order"
;;
"ads_course_review")
    /opt/module/hive/bin/hive -e "use edu;$ads_course_review"
;;
"ads_order_by_subject")
	/opt/module/hive/bin/hive -e "use edu;$ads_order_by_subject_sql"
;;
"ads_order_by_category")
	/opt/module/hive/bin/hive -e "use edu;$ads_order_by_category_sql"
;;
"ads_test_paper")
	/opt/module/hive/bin/hive -e "use edu;$ads_test_paper_sql"
;;
"ads_test_course")
	/opt/module/hive/bin/hive -e "use edu;$ads_test_course_sql"
;;
"ads_course_order" )
    /opt/module/hive/bin/hive -e "$ads_course_order"
;;
"ads_course_review")
    /opt/module/hive/bin/hive -e "$ads_course_review"
;;
"ads_order_integration")
    /opt/module/hive/bin/hive -e "use edu;${ads_order_integration_sql};"
;;
"ads_order_by_province")
    /opt/module/hive/bin/hive -e "use edu;${ads_order_by_province_sql};"
;;
"ads_finish_by_course" )
    /opt/module/hive/bin/hive -e "$ads_finish_by_course"
    ;;
"ads_finish_course_sum" )
    /opt/module/hive/bin/hive -e "$ads_finish_course_sum_sql"
;;
"ads_user_change")
    /opt/module/hive/bin/hive --database edu -e "$ads_user_change"
;;
"ads_user_retention")
    /opt/module/hive/bin/hive --database edu -e "$ads_user_retention"
;;
"ads_user_stats")
    /opt/module/hive/bin/hive --database edu -e "$ads_user_stats"
;;
"ads_paper_score_page")
    /opt/module/hive/bin/hive -e "use edu;$ads_paper_score_page_sql"
;;
"ads_question_accuracy")
    /opt/module/hive/bin/hive -e "use edu;$ads_question_accuracy_sql"
;;
"all" )
    /opt/module/hive/bin/hive -e "use edu;$ads_user_action_sql;$ads_play_by_course_sql;$ads_new_buyer_stats_sql;$ads_finish_course_sum_sql;$ads_age_order_sql;$ads_page_path_sql;$ads_traffic_order_by_channel_sql;$ads_traffic_stats_by_channel_sql;$ads_order_by_subject_sql;$ads_order_by_category_sql;$ads_category_videoplay_retention_sql;$ads_subject_idvideoplay_retention_sql;$ads_course_idvideoplay_retention_sql;$ads_course_order $ads_course_review$ads_play_by_chapter_sql;$ads_test_paper_sql;$ads_test_course_sql;$ads_order_integration_sql;$ads_order_by_province_sql;$ads_finish_by_course;$ads_finish_by_course;$ads_user_stats;$ads_user_retention;$ads_user_change$ads_paper_score_page_sql;$ads_question_accuracy_sql;"
;;
esac
